﻿#include "db.h"
#include "config.h"

#include <qvariant.h>
//#include <QtSql\qsqlerror.h>
#include<qsqlerror.h>
#include<qtextcodec.h>
// GYSCADADB instance;
GYSCADADB* GYSCADADB::instance = NULL;

GYSCADADB::GYSCADADB()
{

    QTextCodec* code=QTextCodec::codecForName("utf-8");

   QTextCodec::setCodecForLocale(code);
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(GYSCADAConfig::getInstance()->getMySQLIp());
    db.setPort(GYSCADAConfig::getInstance()->getMySQLPort());
    db.setDatabaseName(GYSCADAConfig::getInstance()->getMySQLDB());
    db.setUserName(GYSCADAConfig::getInstance()->getMySQLUser());
    db.setPassword(GYSCADAConfig::getInstance()->getMySQLPassword());
    db.open();
}

GYSCADADB::~GYSCADADB()
{
    if(db.isOpen()) {
        db.close();
    }
}

GYSCADADB* GYSCADADB::getInstance()
{
    if (GYSCADADB::instance == NULL) {
        GYSCADADB::instance = new GYSCADADB;
    }

    return GYSCADADB::instance;
}

bool GYSCADADB::isOpen()
{
    return db.isOpen();
}

QString GYSCADADB::lastError()
{
    return db.lastError().text();
}

bool GYSCADADB::verify(const QString& userName, const QString& password)
{
    QString sql = QString("SELECT * FROM %1 AS user WHERE user.name = '%2' AND user.password = MD5('%3') AND user.active = 1")
        .arg(GYSCADAConfig::getInstance()->getMySQLUserTable(), userName, password);
    QSqlQuery query(sql);

    return getQueryResult(query);
}

QString GYSCADADB::getStafferName(const QString& userName, const QString& password)
{
    QString sql = QString(
        "SELECT staffer.name "
        "FROM %1 AS staffer "
        "WHERE staffer.userId = ( "
        "   SELECT user.id "
        "   FROM %2 AS user "
        "   WHERE user.name = '%3' AND "
        "         user.password = MD5('%4') AND "
        "         user.active = 1 "
        ")").arg(GYSCADAConfig::getInstance()->getMySQLStafferTable(),
                 GYSCADAConfig::getInstance()->getMySQLUserTable(),
                 userName,
                 password);
    QSqlQuery query(sql);

    QString stafferName;
    if (getQueryResult(query, stafferName)) {
        return stafferName;
    } else {
        return QString();
    }
}

int GYSCADADB::getUserId(const QString& userName, const QString& password)
{
    QString sql = QString("SELECT id FROM %1 AS user WHERE user.name = '%2' AND user.password = MD5('%3') AND user.active = 1")
        .arg(GYSCADAConfig::getInstance()->getMySQLUserTable(), userName, password);
    QSqlQuery query(sql);

    int userId;
    if (getQueryResult(query, userId)) {
        return userId;
    } else {
        return 0;
    }
}

bool GYSCADADB::record(QList<GYSCADALogItem>& logItems)
{
    if (logItems.isEmpty()) {
        return true;
    }

    QString sql = QString(
        "INSERT INTO %1 (userId, operation, result, equipment, timestamp) VALUES ")
        .arg(GYSCADAConfig::getInstance()->getMySQLLogTable());
    for (QList<GYSCADALogItem>::iterator it = logItems.begin();
         it != logItems.end();
         ++it) {
        QString value = QString(
            "(%1, '%2', '%3', '%4', '%5'),")
            .arg(QString::number(it->userId), it->operation, it->result, it->equipment, it->timestamp.toString("yyyy-MM-dd hh:mm:ss"));
        sql += value;
    }
    sql[sql.size()-1] = ';';
    QSqlQuery query;

    return query.exec(sql);
}

bool GYSCADADB::getQueryResult(QSqlQuery& query)
{
    if (query.isActive() && query.size() > 0) {
        return true;
    } else {
        return false;
    }
}

bool GYSCADADB::getQueryResult(QSqlQuery& query, QString& result)
{
    if (query.isActive() && query.size() > 0) {
        while (query.next()) {
            QVariant var = query.value(0);
            result = var.toString();
        }
        return true;
    } else {
        return false;
    }
}

bool GYSCADADB::getQueryResult(QSqlQuery& query, int& result)
{
    if (query.isActive() && query.size() > 0) {
        while (query.next()) {
            QVariant var = query.value(0);
            result = var.toInt();
        }
        return true;
    } else {
        return false;
    }
}
